﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DataCom;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Globalization;

namespace DataCommunication
{
    public class LineInfoDataAccess : ILineInfoDataAccess
    {
        /// <summary>
        /// Get Line Info
        /// </summary>
        /// <param name="lineCode"></param>
        /// <returns></returns>
        public DataTable GetLineInfo(string lineCode)
        {
            int rowAffected = 0;

            string connectionStr = ConfigurationManager.AppSettings["DBConnectionString"];

            SqlConnection connection = new SqlConnection(connectionStr);
            SqlCommand command = new SqlCommand();
            command.Connection = connection;

            SqlDataAdapter dataAdapter = new SqlDataAdapter();
            dataAdapter.SelectCommand = command;

            connection.Open();

            if (string.IsNullOrWhiteSpace(lineCode))
            {
                command.CommandText = "select LineCode, MachineType,NumTime from dbo.LineInfo";
            }
            else
            {
                command.CommandText = "select LineCode, MachineType,NumTime from dbo.LineInfo where LineCode = @LineCode";

                command.Parameters.Clear();
                command.Parameters.AddWithValue("@LineCode", lineCode);
            }

            DataSet dsLineInfo = new DataSet();
            dsLineInfo.Locale = CultureInfo.InvariantCulture;
            rowAffected = dataAdapter.Fill(dsLineInfo);

            command.Parameters.Clear();
            command.Dispose();

            if (connection.State != ConnectionState.Closed)
            {
                connection.Close();
            }

            return dsLineInfo.Tables[0];
        }
        /// <summary>
        /// Get Line Info by NumTime
        /// </summary>
        /// <param name="lineCode"></param>
        /// <param name="NumTime"></param>
        /// <returns></returns>
        public DataTable GetLineInfo_New(string lineCode,string NumTime )
        {
            int rowAffected = 0;

            string connectionStr = ConfigurationManager.AppSettings["DBConnectionString"];

            SqlConnection connection = new SqlConnection(connectionStr);
            SqlCommand command = new SqlCommand();
            command.Connection = connection;

            SqlDataAdapter dataAdapter = new SqlDataAdapter();
            dataAdapter.SelectCommand = command;

            connection.Open();

            if (string.IsNullOrWhiteSpace(lineCode))
            {
                command.CommandText = "select LineCode, MachineType,NumTime from dbo.LineInfo";
            }
            else
            {
                command.CommandText = "select LineCode, MachineType,NumTime from dbo.LineInfo where LineCode = @LineCode and NumTime=@NumTime";

                command.Parameters.Clear();
                command.Parameters.AddWithValue("@LineCode", lineCode);
                command.Parameters.AddWithValue("@NumTime", NumTime);
            }

            DataSet dsLineInfo = new DataSet();
            dsLineInfo.Locale = CultureInfo.InvariantCulture;
            rowAffected = dataAdapter.Fill(dsLineInfo);

            command.Parameters.Clear();
            command.Dispose();

            if (connection.State != ConnectionState.Closed)
            {
                connection.Close();
            }

            return dsLineInfo.Tables[0];
        }

        /// <summary>
        /// Update Line Info
        /// </summary>
        /// <param name="dtLineInfo"></param>
        /// <param name="dtLineShift"></param>
        /// <returns></returns>
        public bool UpdateLineInfo(DataTable dtLineInfo, DataTable dtLineShift)
        {
            bool result = true;
            string connectionStr = ConfigurationManager.AppSettings["DBConnectionString"];

            SqlConnection connection = new SqlConnection(connectionStr);
            SqlCommand command = new SqlCommand();
            command.Connection = connection;
            connection.Open();

            try
            {
                command.Transaction = connection.BeginTransaction("Trans");

                command.CommandText = "delete from dbo.LineInfo";
                command.ExecuteNonQuery();

                command.CommandText = "delete from dbo.LineShift";
                command.ExecuteNonQuery();

                command.CommandText = "insert into dbo.LineInfo (LineCode, MachineType,NumTime ) values (@LineCode, @MachineType,@NumTime)";

                foreach (DataRow row in dtLineInfo.Rows)
                {
                    if (row.RowState != DataRowState.Deleted
                        && row.RowState != DataRowState.Detached)
                    {
                        string lineCode = row["LineCode"].ToString();
                        string machineType = row["MachineType"].ToString();
                        string NumTime = row["NumTime"].ToString();

                        command.Parameters.Clear();
                        command.Parameters.AddWithValue("@LineCode", lineCode);
                        command.Parameters.AddWithValue("@MachineType", machineType);
                        command.Parameters.AddWithValue("@NumTime", NumTime);

                        command.ExecuteNonQuery();
                    }
                }

                command.CommandText = "insert into dbo.LineShift (LineCode, Shift, StartTime, EndTime, RestTime, PlanNum,NumTime) values (@LineCode, @Shift, @StartTime, @EndTime, @RestTime, @PlanNum,@NumTime)";

                foreach (DataRow row in dtLineShift.Rows)
                {
                    if (row.RowState != DataRowState.Deleted
                        && row.RowState != DataRowState.Detached)
                    {
                        string lineCode = row["LineCode"].ToString();
                        string Shift = row["Shift"].ToString();
                        string StartTime = row["StartTime"].ToString();
                        string EndTime = row["EndTime"].ToString();
                        long restTime = 0;
                        long.TryParse(row["RestTime"].ToString(), out restTime);
                        long planNum = 0;
                        long.TryParse(row["PlanNum"].ToString(), out planNum);
                        string NumTime = row["NumTime"].ToString();

                        command.Parameters.Clear();
                        command.Parameters.AddWithValue("@LineCode", lineCode);
                        command.Parameters.AddWithValue("@Shift", Shift);
                        command.Parameters.AddWithValue("@StartTime", StartTime);
                        command.Parameters.AddWithValue("@EndTime", EndTime);
                        command.Parameters.AddWithValue("@RestTime", restTime);
                        command.Parameters.AddWithValue("@PlanNum", planNum);
                        command.Parameters.AddWithValue("@NumTime", NumTime);

                        command.ExecuteNonQuery();
                    }
                }

                command.Transaction.Commit();
            }
            catch (Exception ex)
            {
                result = false;

                try
                {
                    command.Transaction.Rollback();
                }
                catch (Exception ex2)
                {
                }
            }

            command.Dispose();

            if (connection.State != ConnectionState.Closed)
            {
                connection.Close();
            }

            return result;
        }

        /// <summary>
        /// Get Comment Info
        /// </summary>
        /// <returns></returns>
        public DataTable GetCommentInfo()
        {
            int rowAffected = 0;

            string connectionStr = ConfigurationManager.AppSettings["DBConnectionString"];

            SqlConnection connection = new SqlConnection(connectionStr);
            SqlCommand command = new SqlCommand();
            command.Connection = connection;

            SqlDataAdapter dataAdapter = new SqlDataAdapter();
            dataAdapter.SelectCommand = command;

            connection.Open();

            command.CommandText = "select Comment from dbo.CommentInfo";

            DataSet dsCommentInfo = new DataSet();
            dsCommentInfo.Locale = CultureInfo.InvariantCulture;
            rowAffected = dataAdapter.Fill(dsCommentInfo);

            command.Parameters.Clear();
            command.Dispose();

            if (connection.State != ConnectionState.Closed)
            {
                connection.Close();
            }

            return dsCommentInfo.Tables[0];
        }

        /// <summary>
        /// Update Comment Info
        /// </summary>
        /// <param name="dtCommentInfo"></param>
        /// <returns></returns>
        public bool UpdateCommentInfo(DataTable dtCommentInfo)
        {
            bool result = true;

            string connectionStr = ConfigurationManager.AppSettings["DBConnectionString"];

            SqlConnection connection = new SqlConnection(connectionStr);
            SqlCommand command = new SqlCommand();
            command.Connection = connection;
            connection.Open();

            try
            {
                command.Transaction = connection.BeginTransaction("Trans");

                command.CommandText = "delete from dbo.CommentInfo";
                command.ExecuteNonQuery();

                command.CommandText = "insert into dbo.CommentInfo (Comment) values (@Comment)";

                foreach (DataRow row in dtCommentInfo.Rows)
                {
                    if (row.RowState != DataRowState.Deleted
                        && row.RowState != DataRowState.Detached)
                    {
                        string comment = row["Comment"].ToString();

                        command.Parameters.Clear();
                        command.Parameters.AddWithValue("@Comment", comment);

                        command.ExecuteNonQuery();
                    }
                }

                command.Transaction.Commit();
            }
            catch (Exception ex)
            {
                result = false;

                try
                {
                    command.Transaction.Rollback();
                }
                catch (Exception ex2)
                {
                }
            }

            command.Dispose();

            if (connection.State != ConnectionState.Closed)
            {
                connection.Close();
            }

            return result;
        }
    }
}
